﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODEL;

namespace KPIS.GERP.SECURITY.DAL
{
    public class ApprovePathDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ApprovePathDAL()
        {

        }
        public DataTable GetFull(string process)
        {
            string strSQL;
            
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@process", process);

                strSQL = "select app.approve_path_seq, app.process, app.step_no, app.approver_type"
                    + ", app.exec_pos_group_seq, app.pos_seq"

                    + ", exe.exec_pos_group_name"
                    + ", concat(pos.pos_ref, ' : ', pos.pos_name) as pos_text"

                    + " from sys_approve_path app"
                    + " left join sys_exec_pos_group exe on app.exec_pos_group_seq = exe.exec_pos_group_seq"
                    + " left join hrm_mp_positions pos on app.pos_seq = pos.pos_seq"
                    + " where 0 = 0"
                    + (process != null ? " and app.process = @process" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public void Insert(string process
            , Nullable<int> step_no
            , string approver_type //'1=ผู้บังคับบัญชา, 2=ระบุตำแหน่ง'
            , Nullable<int> exec_pos_group_seq
            , Nullable<int> pos_seq
            , int created_by)
        {
            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@process", process);
                dbManager.AddParameters(1, "@step_no", step_no);
                dbManager.AddParameters(2, "@approver_type", approver_type);
                dbManager.AddParameters(3, "@exec_pos_group_seq", exec_pos_group_seq);
                dbManager.AddParameters(4, "@pos_seq", pos_seq);

                dbManager.AddParameters(5, "@CreatedBy", created_by);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO sys_approve_path"
                    + "(process, step_no, approver_type, exec_pos_group_seq, pos_seq, created_by, created_when)"
                    + " VALUES (@process, @step_no, @approver_type, @exec_pos_group_seq, @pos_seq, @CreatedBy, @CreatedWhen)";
         
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int approve_path_seq
            , string process
            , Nullable<int> step_no
            , string approver_type //'1=ผู้บังคับบัญชา, 2=ระบุตำแหน่ง'
            , Nullable<int> exec_pos_group_seq
            , Nullable<int> pos_seq
            , int updated_by)
        {            
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@approve_path_seq", approve_path_seq);
                dbManager.AddParameters(1, "@process", process);
                dbManager.AddParameters(2, "@step_no", step_no);
                dbManager.AddParameters(3, "@approver_type", approver_type);
                dbManager.AddParameters(4, "@exec_pos_group_seq", exec_pos_group_seq);
                dbManager.AddParameters(5, "@pos_seq", pos_seq);

                dbManager.AddParameters(6, "@UpdatedBy", updated_by);
                dbManager.AddParameters(7, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE sys_approve_path"
                    + " SET process = @process"
                        + ", step_no = @step_no"
                        + ", approver_type = @approver_type"
                        + ", exec_pos_group_seq = @exec_pos_group_seq"
                        + ", pos_seq = @pos_seq"

                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE approve_path_seq = @approve_path_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@Sequence", Sequence);

                strSQL = "delete from sys_approve_path"
                    + " WHERE approve_path_seq = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
